/*
 * Copyright 2008 Carnegie Mellon University
 * Licensed under the Apache License, Version 2.0 (the "License"); 
 * you may not use this file except in compliance with the License.
 * You may obtain a copy of the License at
 *  
 *   http://www.apache.org/licenses/LICENSE-2.0
 *
 * Unless required by applicable law or agreed to in writing, 
 * software distributed under the License is distributed on an
 * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
 * KIND, either express or implied. See the License for the
 * specific language governing permissions and limitations
 * under the License.
 */
package edu.cmu.lti.sepia.data.db;

import java.io.BufferedReader;
import java.io.IOException;
import java.io.InputStream;
import java.io.InputStreamReader;
import java.io.OutputStream;
import java.io.Writer;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;

import org.json.JSONArray;
import org.json.JSONException;
import org.json.JSONObject;

import jxl.Workbook;
import jxl.WorkbookSettings;
import jxl.write.Label;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;
import jxl.write.WriteException;
import jxl.write.biff.RowsExceededException;
import edu.cmu.lti.sepia.data.Lang;
import edu.cmu.lti.sepia.infra.eval.model.IRJudgmentResult;
import edu.cmu.lti.sepia.infra.eval.model.IRJudgmentResultSet;
import edu.cmu.lti.sepia.infra.eval.model.IRJudgmentResults;
import edu.cmu.lti.sepia.infra.eval.model.IRJudgmentUnit;

/**
 * 
 * Manages IR Judgment result object in a DB.
 * 
 * @see IRJudgmentResult
 * @author Hideki Shima
 *
 */
public class IRJudgmentResultDAO {
  
  // Can find by judgment_id or (topic_id and document_id). Multiple entry is returned due to multiple assessors
  public static IRJudgmentResults findResult( SQL sql, String table, IRJudgmentUnit unit ) 
  throws SQLException {
    int judgmentId = unit.getJudgmentId();
    if (judgmentId<=0) {
      unit = IRJudgmentUnitDAO.findJudgmentUnitByTopicAndDocument(sql, table, unit.getTopicId(), unit.getDocumentId());
      judgmentId = unit.getJudgmentId();
    } else if (unit.getDocumentId()==null || unit.getTopicId()==null ||
            unit.getDocumentId().length()==0 || unit.getTopicId().length()==0 ) {
      // Problem!
      unit = IRJudgmentUnitDAO.findJudgmentUnitByTopicAndDocument(sql, table, unit.getTopicId(), unit.getDocumentId());
    }
    String query = Query.generate( Query.FIND_IR_JUDGMENT_RESULT_BY_ID, table );
    PreparedStatement ps = sql.getConnection().prepareStatement(query);
    ResultSet rs = null;
    IRJudgmentResults result = new IRJudgmentResults();
    
    ps.setInt(1, judgmentId);
    rs = sql.executeSelect(ps);
    if ( rs == null ) System.out.println( ps );
    while ( rs.next() ) {
      IRJudgmentResult r = rsToObject(rs);
      r.setUnit(unit);
      result.add( r );
    }
    rs.close();
    ps.close();
    return result;
  }
  
  public static IRJudgmentResult findResult( SQL sql, String table, IRJudgmentUnit unit, int userId ) 
  throws SQLException {
    IRJudgmentResults results = findResult(sql, table, unit);
    for (IRJudgmentResult result : results) {
      if (result.getUserId()==userId) {
        return result;
      }
    }
    return null;
  }
  
  //list of documents to be evaluated
  public static IRJudgmentResults findResultsByPoolAndTopic( SQL sql, String table, int poolId, String topicId, int userId )
  throws SQLException {
    IRJudgmentResults results = new IRJudgmentResults();
    List<IRJudgmentUnit> units = IRJudgmentUnitDAO.findJudgmentUnitsByPoolAndTopic(sql, table, poolId, topicId);
    for (IRJudgmentUnit u : units) {
      IRJudgmentResults resultsByAssessors = findResult(sql, table, u);
      IRJudgmentResult myJudgment = null;
      boolean evaluatedByOthers = false;
      for ( IRJudgmentResult r : resultsByAssessors ) {
        if ( userId == r.getUserId() ) {
          myJudgment = r;
        } else {
          evaluatedByOthers = true;
        }
      }
      if (myJudgment==null) {
        myJudgment = new IRJudgmentResult(u, userId, "", "", -1);
      }
      myJudgment.setEvaluatedByOthers(evaluatedByOthers);
      results.add(myJudgment);
    }
    return results;
  }
  
  // "UPDATE ... ON DUPLICATE KEY" doesn't work when there are multiple indices on the table
  public static void updateResult( SQL sql, String table, IRJudgmentResult result ) 
  throws SQLException {
    
    IRJudgmentUnit unit = result.getUnit();
    if (unit.getJudgmentId()<=0) {
      unit = IRJudgmentUnitDAO.findJudgmentUnitByTopicAndDocument(sql, table, unit.getTopicId(), unit.getDocumentId());
    }
    
    IRJudgmentResult existingResult = findResult(sql,table,result.getUnit(),result.getUserId());
    PreparedStatement ps;
    if (existingResult!=null) { // record already exists
      String query = Query.generate( Query.UPDATE_IR_JUDGMENT_RESULT, table );
      ps = sql.getConnection().prepareStatement(query);
      ps.setString(1, result.getJudgment());
      ps.setString(2, result.getEvidence());
      ps.setInt(3, result.getTime());
      ps.setInt(4, unit.getJudgmentId());
      ps.setInt(5, result.getUserId());
      ps.executeUpdate();
//      System.out.println("UPD\t"+ps);
    } else {
      String query = Query.generate( Query.INSERT_IR_JUDGMENT_RESULT, table );
      ps = sql.getConnection().prepareStatement(query);
      ps.setInt(1, unit.getJudgmentId());
      ps.setInt(2, result.getUserId());
      ps.setString(3, result.getJudgment());
      ps.setString(4, result.getEvidence());
      ps.setInt(5, result.getTime());
//      System.out.println("INS\t"+ps);
      ps.execute();
    }
    ps.close();
  }
  
  // Since the qrels are huge, don't store them into objects
  public static void exportQrels( SQL sql, String table, Writer out ) 
  throws SQLException, IOException {
    String query = Query.generate( Query.GET_IR_JUDGMENT_RESULTS, table );
    PreparedStatement ps = sql.getConnection().prepareStatement(query);
    ResultSet rs = null;
    
    rs = sql.executeSelect(ps);
    if ( rs == null ) {
      System.out.println( ps );
      out.write("No judgment result available for the dataset \""+table+"\"");
    } else {
        while ( rs.next() ) {
        String topicId = rs.getString(1);
        String docId = rs.getString(2);
        String judgment = rs.getString(3);
        String user = rs.getString(4);
        int time = rs.getInt(5);
        String evidence = rs.getString(6).replaceAll("\n", "\\\\n");
        out.write( topicId+" "+docId+" "+judgment+" "+user+" "+time+" "+evidence+"\n" );
      }
      rs.close();
    }
    ps.close();
  }
  

  private static String[] qrelsColumns = {"Topic ID","Document ID","Judgment","Assessor","Time (msec)","Evidence"};
  
  // Since the qrels are huge, don't store them into objects
  public static void exportQrelsInExcel( SQL sql, String table, String langString, OutputStream os ) 
  throws SQLException, IOException, RowsExceededException, WriteException {
    String query = Query.generate( Query.GET_IR_JUDGMENT_RESULTS, table );
    PreparedStatement ps = sql.getConnection().prepareStatement(query);
    ResultSet rs = null;
    
    rs = sql.executeSelect(ps);
    if ( rs == null ) {
      System.out.println( ps );
      //out.write("No judgment result available for the dataset \""+table+"\"");
    } else {
      WorkbookSettings ws = new WorkbookSettings();
      Lang lang = Lang.valueOf(langString.toUpperCase());
      ws.setLocale(lang.getLocale());
      //ws.setEncoding("Windows-31J");
      
      WritableWorkbook workbook = Workbook.createWorkbook(os, ws);
      WritableSheet sheet = workbook.createSheet("Sheet1", 0);
      
      sheet.addCell( new Label(0,0,"Note: see all sheets in case more than 65536 judgments are done.") );
      for ( int xi=0; xi<qrelsColumns.length; xi++ ) {
        sheet.addCell( new Label(xi,1,qrelsColumns[xi]) );
      }
      
      int sheetIndex = 0;
      int y = 1;
      while ( rs.next() ) {
        if (y%65537==65536) {
          sheetIndex++;
          sheet = workbook.createSheet("Sheet"+(sheetIndex+1), sheetIndex);
          for ( int xi=0; xi<qrelsColumns.length; xi++ ) {
            sheet.addCell( new Label(xi,0,qrelsColumns[xi]) );
          }
          y = 0;
        }
        y++;
        String topicId = rs.getString(1);
        String docId = rs.getString(2);
        String judgment = rs.getString(3);
        String user = rs.getString(4);
        int time = rs.getInt(5);
        String evidence = rs.getString(6).replaceAll("\n", "\\\\n");
        
        int x=0;
        sheet.addCell( new Label(x++,y,topicId) );
        sheet.addCell( new Label(x++,y,docId) );
        sheet.addCell( new Label(x++,y,judgment) );
        sheet.addCell( new Label(x++,y,user) );
        sheet.addCell( new Label(x++,y,time+"") );
        sheet.addCell( new Label(x++,y,evidence) );
      }
      workbook.write();
      workbook.close();
      rs.close();
    }
    ps.close();
  }
  
  private static IRJudgmentResult rsToObject( ResultSet rs ) {
    try {
      IRJudgmentResult r = new IRJudgmentResult( rs.getInt(1), rs.getInt(2),
              rs.getString(3), rs.getString(4), rs.getInt(5));
      return r;
    } catch ( Exception e ) {
      e.printStackTrace();
      return null;
    }
  }
  
  public static JSONObject getIRJudgmentResults( List<IRJudgmentResult> results ) throws JSONException {
    JSONObject json = new JSONObject();
    JSONArray jsonArray = new JSONArray();
    for (IRJudgmentResult result : results) {
      JSONObject j = new JSONObject();
      j.put("judgment_id", result.getJudgmentId());
      j.put("doc_id", result.getDocId());
      j.put("judgment", result.getJudgment());
      j.put("evidence", result.getEvidence());
      j.put("evaluated_by_others", (result.isEvaluatedByOthers()?1:0));
      jsonArray.put(j);
    }
    json.put("ir_judgments", jsonArray);
    return json;
  }
  
  public static IRJudgmentResultSet deserialize( InputStream is ) throws Exception {
    IRJudgmentResultSet results = null;
    
    InputStreamReader isr = null;
    BufferedReader br = null;
    try {
      results = new IRJudgmentResultSet();
      isr = new InputStreamReader(is,"utf-8");
      br = new BufferedReader(isr);
      String line = null;
      while ((line=br.readLine())!=null) {
        line = line.trim();
        String[] items = line.split("\\s+");
        boolean withEvidence = items.length==4;
        String topicId = items[0];
        String docid = items[1];
        String relevance = items[2];
        String evidence = withEvidence?items[3]:null;
        IRJudgmentResults result = results.get(topicId);
        if (result==null) {
          result = new IRJudgmentResults();
        }
        IRJudgmentUnit unit = new IRJudgmentUnit(-1, -1, topicId, docid, 0D);
        IRJudgmentResult irresult = new IRJudgmentResult(unit, -1, relevance, evidence, -1);
        result.add(irresult);
        results.put(topicId, result);
      }
    } catch (Exception e) {
      e.printStackTrace();
    } finally {
      try {
        if (br!=null){ 
          br.close();
        }
        if (isr!=null){ 
          isr.close();
        }
      } catch (IOException e) {
      }
    }
    return results;
  }
  
}
